﻿using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Collections.Generic;
using System;
using System.Web.UI.WebControls;
using LCUV;
using System.Collections;
using System.Web;
using System.Web.UI;
using System.Text;
using System.Reflection;
using System.IO;
using System.Net.Mail;
using System.Net;
using sWarranty;
using System.Web.Configuration;
using Oracle.DataAccess.Client;

namespace LCUV
{
    public static class DBAction
    {
        //string error = (RunStore("select pk_som_mst.FN_INCT_MD_UPL_VAL('" + Scheme_ID + "','" + Model + "','" + Point + "','" + Amount + "') from dual"));
        public const int PageSize = 13;
        public const int BU = 0;//mac dinh user View HA , 0 = all, another thi view DB
        public const bool LogUser = true ;
        public const string ReloadCommandText = "Reload";

        public static String cnn = WebConfigurationManager.ConnectionStrings["tnsString"].ConnectionString;
        public static OracleConnection con;

        public static string CheckFileIsPPTFile(HttpPostedFile file)
        {
            // if the file is null or has no content it's obviously a dirty wrongun
            if (file == null) return "file is invalid, upload must have failed";
            if (file.ContentLength == 0) return "no file specified";

            // get the filename extension (e.g .jpg, .gif, etc) from the filename
            var extension = Path.GetExtension(file.FileName).ToLower();

            // we're only supporting jpg, png and gif in this (you can support more if you 
            // like though)
            if (extension != ".ppt")
                return "unsupported image type";

            // if we got this far the file looks good, so return an empty string (which 
            // means everything ok)
            return string.Empty;
        }
        public static string CheckExtUploadfile(HttpPostedFile file)
        {
            // if the file is null or has no content it's obviously a dirty wrongun
            if (file == null) return "file is invalid, upload must have failed";
            if (file.ContentLength == 0) return "no file specified";

            // get the filename extension (e.g .jpg, .gif, etc) from the filename
            var extension = Path.GetExtension(file.FileName).ToLower();

            // we're only supporting jpg, png and gif in this (you can support more if you 
            // like though)
            if (extension != ".jpg" && extension != ".gif" && extension != ".png" && extension != ".ppt")
                return "unsupported image type";

            // if we got this far the file looks good, so return an empty string (which 
            // means everything ok)
            return string.Empty;
        }
        public static string CheckFileIsAnImage(HttpPostedFile file)
        {
            // if the file is null or has no content it's obviously a dirty wrongun
            if (file == null) return "file is invalid, upload must have failed";
            if (file.ContentLength == 0) return "no file specified";

            // get the filename extension (e.g .jpg, .gif, etc) from the filename
            var extension = Path.GetExtension(file.FileName).ToLower();

            // we're only supporting jpg, png and gif in this (you can support more if you 
            // like though)
            if (extension != ".jpg" && extension != ".gif" && extension != ".png")
                return "unsupported image type";

            // if we got this far the file looks good, so return an empty string (which 
            // means everything ok)
            return string.Empty;
        }
        public static DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
        {
            DataTable dtReturn = new DataTable();


            // column names
            PropertyInfo[] oProps = null;


            if (varlist == null) return dtReturn;


            foreach (T rec in varlist)
            {
                // Use reflection to get property names, to create table, Only first time, others will follow
                if (oProps == null)
                {
                    oProps = ((Type)rec.GetType()).GetProperties();
                    foreach (PropertyInfo pi in oProps)
                    {
                        Type colType = pi.PropertyType;


                        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }


                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                    }
                }


                DataRow dr = dtReturn.NewRow();


                foreach (PropertyInfo pi in oProps)
                {
                    dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
                    (rec, null);
                }


                dtReturn.Rows.Add(dr);
            }
            return dtReturn;
        }

        public static string strInitFlash(string flashURL, int width, int height, string _align)
        {
            //<embed src="./image/flash/vietnam.swf" allowScriptAccess="always" allowfullscreen="true" quality="high" align="center"  type="application/x-shockwave-flash" 
            //width="100% " height="100%" style="overflow:hidden">
            //</embed>
            string retVal = "<embed src='" + flashURL + "' allowScriptAccess='always' allowfullscreen='true' quality='high' align='" + _align + "'  type='application/x-shockwave-flash' width='100%' z-index:'-100' height='100%' style='overflow:hidden'></embed>";
            //retVal += "<PARAM NAME=movie VALUE='" + flashURL + "'>";
            //retVal += "<PARAM NAME=quality VALUE=high>";
            //retVal += "<EMBED src='" + flashURL + "' quality=high WIDTH='" + width + "' height='" + height + "'";
            //retVal += "NAME='MyMovieName' ALIGN='" + _align + "' TYPE='application/x-shockwave-flash' PLUGINSPAGE='http://www.macromedia.com/go/getflashplayer'>";
            //retVal += "</EMBED>";
            //retVal += "</OBJECT>";

            return retVal;
        }
        public static string DisplayTime(DateTime dt)
        {
            if (dt != Convert.ToDateTime("1/1/1911"))
            {
                var ts = new TimeSpan(DateTime.Now.Ticks - dt.Ticks);
                double delta = Math.Abs(ts.TotalSeconds);

                const int SECOND = 1;
                const int MINUTE = 60 * SECOND;
                const int HOUR = 60 * MINUTE;
                const int DAY = 24 * HOUR;
                const int MONTH = 30 * DAY;

                if (delta < 0)
                {
                    return "not yet";
                }
                if (delta < 1 * MINUTE)
                {
                    return ts.Seconds == 1 ? "one second ago" : ts.Seconds + " seconds ago";
                }
                if (delta < 2 * MINUTE)
                {
                    return "a minute ago";
                }
                if (delta < 45 * MINUTE)
                {
                    return ts.Minutes + " minutes ago";
                }
                if (delta < 90 * MINUTE)
                {
                    return "an hour ago";
                }
                if (delta < 24 * HOUR)
                {
                    return ts.Hours + " hours ago";
                }
                if (delta < 48 * HOUR)
                {
                    return "yesterday";
                }
                if (delta < 30 * DAY)
                {
                    return ts.Days + " days ago";
                }
                if (delta < 12 * MONTH)
                {
                    int months = Convert.ToInt32(Math.Floor((double)ts.Days / 30));
                    return months <= 1 ? "one month ago" : months + " months ago";
                }
                else
                {
                    int years = Convert.ToInt32(Math.Floor((double)ts.Days / 365));
                    return years <= 1 ? "one year ago" : years + " years ago";
                }
            }
            else
                return "";
        }
        //public static void FillToListBox(ListItemCollection lstCmds)
        //{
        //    lstCmds.Clear();
        //    DataTable dtItems = GetCMDIDByParentID(0);
        //    foreach (DataRow row in dtItems.Rows)
        //    {
        //        ListItem item = new ListItem();
        //        item.Value = row["CmdID"].ToString();
        //        item.Text = row["Name"].ToString();
        //        item.Attributes.Add("Level", "0");
        //        lstCmds.Add(item);
        //        LoadCmdItem(lstCmds, item);
        //    }
        //}
        //public static void FillZoneToListBox(ListItemCollection lstCmds, string lang)
        //{
        //    lstCmds.Clear();
        //    DataTable dtItems = GetZoneByParentID(0,lang);
        //    foreach (DataRow row in dtItems.Rows)
        //    {
        //        ListItem item = new ListItem();
        //        item.Value = row["menuID"].ToString();
        //        item.Text = row["Name"].ToString();
        //        item.Attributes.Add("Level", "0");
        //        lstCmds.Add(item);
        //        LoadZoneItem(lstCmds, item, lang );
        //    }
        //}
        //public static DataTable GetZoneByParentID(int parentID, string lang)
        //{
        //    Entities db = new Entities();
        //    DataTable dtt = new DataTable();
        //    var uiroi = from t in db.CMS_UserZoneRoles
        //                where t.CMS_Zone.ParentID == parentID && t.CMS_Zone.Lang == lang 
        //                select t.CMS_Zone ;
        //    if (uiroi.Count() > 0)
        //        return ToDataTable(db, uiroi);
        //    else
        //        return dtt;
        //}
        //public static DataTable GetCMDIDByParentID(int parentID)
        //{
        //    DataTable dt = null;
        //    Entities db = new Entities();
        //    DataTable dtt = new DataTable();
        //    var uiroi = from t in db.Main_Cmds
        //                where t.ParentID == parentID
        //                select t;
        //    if (uiroi.Count() > 0)
        //        return ToDataTable(db, uiroi);
        //    else
        //        return dtt;
        //}
        //private static void LoadZoneItem(ListItemCollection lstCmds, ListItem curItem,string lang)
        //{
        //    int level = Convert.ToInt32(curItem.Attributes["Level"]);
        //    level += 1;
        //    int curID = Convert.ToInt32(curItem.Value);
        //    DataTable dtChild = GetZoneByParentID(curID,lang );
        //    foreach (DataRow row in dtChild.Rows)
        //    {
        //        ListItem childItem = new ListItem();
        //        childItem.Text = MiscUtility.StringIndent(level) + row["Name"].ToString();
        //        childItem.Value = row["menuID"].ToString();
        //        childItem.Attributes.Add("Level", level.ToString());
        //        lstCmds.Add(childItem);
        //        LoadZoneItem(lstCmds, childItem,lang);
        //    }
        //}
        //private static void LoadCmdItem(ListItemCollection lstCmds, ListItem curItem)
        //{
        //    int level = Convert.ToInt32(curItem.Attributes["Level"]);
        //    level += 1;
        //    int curID = Convert.ToInt32(curItem.Value);
        //    DataTable dtChild = GetCMDIDByParentID(curID);
        //    foreach (DataRow row in dtChild.Rows)
        //    {
        //        ListItem childItem = new ListItem();
        //        childItem.Text = MiscUtility.StringIndent(level) + row["Name"].ToString();
        //        childItem.Value = row["CmdID"].ToString();
        //        childItem.Attributes.Add("Level", level.ToString());
        //        lstCmds.Add(childItem);
        //        LoadCmdItem(lstCmds, childItem);
        //    }
        //}
        //public static int CheckCmdIsSecondLevel(int cmdid)
        //{
        //    Entities db = new Entities();
        //    var u = from t in db.Main_Cmds
        //            where t.ParentID == 0 && t.CmdID == cmdid
        //            select t;
        //    if (u.Count() > 0)
        //        return 1;
        //    else
        //        return 0;
        //}
        //advertise
        //public static string DeletePosition(int _ID)
        //{
        //    try
        //    {
        //        Entities db = new Entities();
        //        var i = from t in db.Adv_Positions
        //                where t.ID == _ID
        //                select t;
        //        db.Adv_Positions.DeleteAllOnSubmit(i);
        //        db.SubmitChanges();
        //        return "";
        //    }
        //    catch (Exception ee)
        //    {
        //        return ("Error at CMS.ContentDB.DeletePosition(): " + ee.Message);
        //    }
        //}

        public static void SetSelected(ListItemCollection lstItems, string selectedValue)
        {
            ListItem item = lstItems.FindByValue(selectedValue);
            if (item != null) item.Selected = true;
        }

        /// <summary>
        /// Sets the selected.
        /// </summary>
        /// <param name="dropList">The drop list.</param>
        /// <param name="selectedValue">The selected value.</param>
        public static void SetSelected(DropDownList dropList, string selectedValue)
        {
            dropList.SelectedIndex = -1;
            SetSelected(dropList.Items, selectedValue);
        }

        /// <summary>
        /// Sets the selected.
        /// </summary>
        /// <param name="list">The list.</param>
        /// <param name="selectedValue">The selected value.</param>
        public static void SetSelected(ListBox list, string selectedValue)
        {
            list.SelectedIndex = -1;
            SetSelected(list.Items, selectedValue);
        }
        public static string SendMail(string fromemail, string toemail, string subject, string body, string mailserver, int port, string username, string pass, bool enablessl, bool isLGmail)
        {
            try
            {
                MailMessage msg = new MailMessage();
                msg.IsBodyHtml = true;
                if (!username.Contains("@"))
                    msg.From = new MailAddress(username + "@lge.com");
                else
                    msg.From = new MailAddress(username);
                if (!toemail.Contains("@"))
                    msg.To.Add(new MailAddress(toemail + "@lge.com"));
                else
                    msg.To.Add(new MailAddress(toemail));
                msg.Subject = subject;
                msg.Body = body;
                if (isLGmail == true)
                {
                    SmtpClient client = new SmtpClient(mailserver, port);//server google
                    client.Credentials = new NetworkCredential(username, pass);
                    client.EnableSsl = enablessl;//google thi fai true, LG la false
                    client.Send(msg);
                }
                else
                {
                    SmtpClient client = new SmtpClient(mailserver, port);
                    client.Credentials = new NetworkCredential(username, pass);
                    client.EnableSsl = enablessl;
                    client.Send(msg);
                }
                return "1";
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null)
                    return ex.InnerException.Message;
                else
                    return "error:SendMail():" + ex.Message;
            }

        }

        public static string GetMenuName(int MenuID)
        {
            try
            {
                Entities db = new Entities();
                var m = from t in db.TB_MENU
                        where t.ID == MenuID
                        select t;
                if (m.Count() > 0)
                    return m.Single().NAME;
                else
                    return "No exist menu ID: " + MenuID;
            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return "Error while load Menu name at GetMenuName(): " + ee.InnerException.Message;
                else
                    return "Error while load Menu name at GetMenuName(): " + ee.Message;
            }
        }
        public static string GetMenuCode(int MenuID)
        {
            try
            {
                Entities db = new Entities();
                var m = from t in db.TB_MENU
                        where t.ID == MenuID
                        select t;
                if (m.Count() > 0)
                    return m.Single().CODE;
                else
                    return "No exist menu ID: " + MenuID;
            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return "Error while load Menu name at GetMenuCode(): " + ee.InnerException.Message;
                else
                    return "Error while load Menu name at GetMenuCode(): " + ee.Message;
            }
        }
        public static string GetSubMenuName(int SubMenuID)
        {
            try
            {
                Entities db = new Entities();
                var m = from t in db.TB_SUB_MENU
                        where t.ID == SubMenuID
                        select t;
                if (m.Count() > 0)
                    return m.Single().NAME;
                else
                    return "No exist SubMenu ID: " + SubMenuID;
            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return "Error while load SubMenu name at GetSubMenuName(): " + ee.InnerException.Message;
                else
                    return "Error while load SubMenu name at GetMenuName(): " + ee.Message;
            }
        }
        public static string GetSubMenuCode(int SubMenuID)
        {
            try
            {
                Entities db = new Entities();
                var m = from t in db.TB_SUB_MENU
                        where t.ID == SubMenuID
                        select t;
                if (m.Count() > 0)
                    return m.Single().CODE;
                else
                    return "No exist SubMenu ID: " + SubMenuID;
            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return "Error while load SubMenu name at GetSubMenuCode(): " + ee.InnerException.Message;
                else
                    return "Error while load SubMenu name at GetSubMenuCode(): " + ee.Message;
            }
        }
        public static string GetMenuOpenType(int MenuID)
        {
            try
            {
                Entities db = new Entities();
                var m = from t in db.TB_MENU
                        where t.ID == MenuID
                        select t;
                if (m.Count() > 0)
                    return m.Single().OPEN_TYPE;
                else
                    return "No exist menu ID: " + MenuID;
            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return "Error while load Menu name at GetOpenType(): " + ee.InnerException.Message;
                else
                    return "Error while load Menu name at GetOpenType(): " + ee.Message;
            }
        }
        public static string GetSubMenuOpenType(int SubMenuID)
        {
            try
            {
                Entities db = new Entities();
                var m = from t in db.TB_SUB_MENU
                        where t.ID == SubMenuID
                        select t;
                if (m.Count() > 0)
                    return m.Single().OPEN_TYPE;
                else
                    return "No exist SubMenu ID: " + SubMenuID;
            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return "Error while load SubMenu name at GetOpenType(): " + ee.InnerException.Message;
                else
                    return "Error while load SubMenu name at GetOpenType(): " + ee.Message;
            }
        }
        public static int GetProvinceID(int districtID)
        {
            Entities db = new Entities();
            var p = from t in db.TB_CM_DISTRICT
                    where t.ID == districtID
                    select new { ID = t.PROVINCE_ID };
            if (p.Count() == 1)
                return p.Single().ID;
            else
                return -1;
        }
        public static string GetAuthorityName(int userID)
        {
            try
            {
                Entities db = new Entities();
                var le = from t in db.TB_USER_LEVEL
                         where t.USER_ID == userID
                         select t;
                if (le.Count() == 1)
                {
                    int x = (le.Single().LEVEL_ID);
                    var name = from t in db.TB_LEVEL
                               where t.ID == x
                               select t;
                    return name.Single().REMARK;
                }
                else
                {
                    return "Error while get Authority Name";
                }

            }
            catch (Exception ee)
            {

                if (ee.InnerException != null)
                    return ee.InnerException.Message;
                else
                    return ee.Message;
            }

        }
        public static string MakeLogID(DateTime d)
        {
            return String.Format("{0:u}", d).ToString();
        }
        public static string GetNewShopCode(int s)
        {
            Entities db = new Entities();
            TB_CM_SHOP_INF sh = new TB_CM_SHOP_INF();
            var _max = (from t in db.TB_CM_SHOP_INF select t.SHOP_CODE.Replace("EASV", "")).Max();
            int _ID = Convert.ToInt32(_max) + 1 + s;
            string st = _ID.ToString();
            while (st.Length < 4)
            {
                st = "0" + st;
            }
            return "EASV" + st;
        }
        public static string GetBillToName(string billtoCode)
        {
            Entities db = new Entities();
            var b = from t in db.TB_CM_BILLTO_INF
                    where t.BILLTO_CODE == billtoCode
                    select t;
            if (b.Count() == 1)
                return b.Single().FULL_NAME;
            else
                return "Error while get BILLTO Name";
        }
        public static string GetCusCodeAddress(string cusCode)
        {
            Entities db = new Entities();
            var b = from t in db.TB_CM_BILLTO_INF
                    where t.CUSTOMER_CODE == cusCode
                    select t;
            if (b.Count() == 1)
                return b.Single().ADDRESS;
            else
                return "Error while get Address of Customer";
        }
        public static string GetProvinceName(int pID)
        {
            Entities db = new Entities();
            var p = from t in db.TB_CM_PROVINCE
                    where t.ID == pID
                    select t;
            if (p.Count() > 0)
                if (p.Single().NAME != null)
                    return p.Single().NAME;
            return "Not exist province " + pID;
        }
        public static string GetDistricName(int dID)
        {
            Entities db = new Entities();
            var p = from t in db.TB_CM_DISTRICT
                    where t.ID == dID
                    select t;
            if (p.Count() > 0)
                if (p.Single().NAME != null)
                    return p.Single().NAME;
            return "Not exist district " + dID;
        }
        public static string GetShopCell1(string shopCode)
        {
            Entities db = new Entities();
            var p = from t in db.TB_CM_SHOP_INF
                    where t.SHOP_CODE == shopCode
                    select t;
            if (p.Count() == 1)
                if (p.Single().CELL_NO_1 != null)
                    return p.Single().CELL_NO_1;
                else
                    return "Error when get Cell of " + shopCode;
            return "Error when get Cell of " + shopCode;

        }
        public static string GetShopName(string shopCode)
        {
            Entities db = new Entities();
            var p = from t in db.TB_CM_SHOP_INF
                    where t.SHOP_CODE == shopCode
                    select t;
            if (p.Count() > 0)
                if (p.Single().SHOP_NAME != null)
                    return p.Single().SHOP_NAME;
            return "Not exist Shop " + shopCode;
        }
        public static string GetBillToByShopCode(string shopCode)
        {
            Entities db = new Entities();
            string st = "";
            var p = from t in db.TB_CM_SHOP_BILLTO
                    where t.SHOP_CODE == shopCode && t.USE_FLAG.ToLower() == "y"
                    select t;
            foreach (var item in p)
            {
                st += item.BILLTO_CODE + ",";
            }
            if (!string.IsNullOrEmpty(st))
                return st;
            else
                return "Not exist Shop" + shopCode;
        }
        public static bool ShopNameExistOrNot(string shName)
        {
            Entities db = new Entities();
            shName = shName.ToLower();
            var s = from t in db.TB_CM_SHOP_INF
                    where t.SHOP_NAME.ToLower() == shName
                    select t;
            if (s.Count() > 0)
                return true;
            else
                return false;
        }
        public static bool ShopCellExistOrNot(string cell1)
        {
            Entities db = new Entities();
            var s = from t in db.TB_CM_SHOP_CELL
                    where t.SHOP_CELL == cell1 && t.USE_FLAG.ToUpper() == "Y"
                    select t;
            if (s.Count() > 0)
                return true;
            else
            {
                //truong hop test data ko dong nhat, se chay them doan nay
                var ss = from t in db.TB_CM_SHOP_INF
                         where t.CELL_NO_1 == cell1 || t.CELL_NO_2 == cell1
                         select t;
                if (ss.Count() > 0)
                    return true;
                else
                    return false;

            }
        }
        public static string RunStore(string query)
        {
            try
            {
                //string query = "select PK_SOM_MST.FN_SN_UPLOAD_VALIDATE('EASV0sdfsd001' , 'W1943SE-PF', '103VNGT0F375', '43534534') from dual";

                //con = new OracleConnection(cnn);
                //con.Open();
                OracleCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = query;
                int t = cmd.ExecuteNonQuery();
                if (t != 1 && t != -1)
                    return t.ToString();
                else
                    return "";
            }
            catch (Exception ee)
            {
                return (ee.Message);
            }
        }
        public static string RunStore2(string y, string bill, string product, string model, string functionName)
        {
            string _bill = "%";
            string _product = "%";
            string _model = "%";
            try
            {
                if (bill != "%")
                    _bill = bill;
                if (product != "%")
                    _product = product;
                if (model != "%")
                    _model = model;
                OracleCommand spcmd = new OracleCommand(functionName);
                spcmd.CommandType = CommandType.StoredProcedure;
                spcmd.Connection = con;
                spcmd.Parameters.Add("p_yyyy", OracleDbType.NVarchar2).Value = y;
                spcmd.Parameters.Add("p_billto_code", OracleDbType.NVarchar2).Value = _bill;
                spcmd.Parameters.Add("p_product", OracleDbType.NVarchar2).Value = _product;
                spcmd.Parameters.Add("p_model", OracleDbType.NVarchar2).Value = _model;
                spcmd.ExecuteNonQuery();
                return "";
            }
            catch (Exception ee)
            {
                return (ee.Message);
            }
        }
        //public static DataTable runStore(string query)
        //{
        //    //chi la template, fai chay tai tung page
        //    try
        //    {
        //        DataTable dt = new DataTable();
        //        OracleCommand command = con.CreateCommand();
        //        command.CommandText = query;
        //        command.CommandType = CommandType.StoredProcedure;
        //        command.CommandText = "PK_SOM_MST.PR_INV_AG_DLY_INQ";

        //        command.Parameters.Add("out_cur", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
        //        command.Parameters.Add("p_w_st_d", OracleDbType.Varchar2, ParameterDirection.Input).Value = "20130304";
        //        command.Parameters.Add("p_w_ed_d", OracleDbType.Varchar2, ParameterDirection.Input).Value = "20130310";
        //        OracleDataAdapter adap = new OracleDataAdapter(command);

        //        adap.Fill(dt);
        //        return dt;
        //    }
        //    catch (Exception)
        //    {
        //        return null;
        //    }
        //}
        public static DataTable runStore(string ProcName, string p_out, string p1, string v1, string p2, string v2, string p3, string v3, string p4, string v4, string p5, string v5, string p6, string v6, string p7, string v7, string p8, string v8, string p9, string v9, string p10, string v10, string p11, string v11, string p12, string v12, string p13, string v13, string p14, string v14, string p15, string v15)
        {
            DataTable dt = new DataTable();
            try
            {
                OracleCommand cmd = DBAction.con.CreateCommand();
                cmd.CommandText = ProcName;
                cmd.CommandType = CommandType.StoredProcedure;
                if (!string.IsNullOrEmpty(p_out))
                    cmd.Parameters.Add("out_cur", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                if (!string.IsNullOrEmpty(p1))
                    cmd.Parameters.Add(p1, OracleDbType.Varchar2, ParameterDirection.Input).Value = v1;
                if (!string.IsNullOrEmpty(p2))
                    cmd.Parameters.Add(p2, OracleDbType.Varchar2, ParameterDirection.Input).Value = v2;
                if (!string.IsNullOrEmpty(p3))
                    cmd.Parameters.Add(p3, OracleDbType.Varchar2, ParameterDirection.Input).Value = v3;
                if (!string.IsNullOrEmpty(p4))
                    cmd.Parameters.Add(p4, OracleDbType.Varchar2, ParameterDirection.Input).Value = v4;
                if (!string.IsNullOrEmpty(p5))
                    cmd.Parameters.Add(p5, OracleDbType.Varchar2, ParameterDirection.Input).Value = v5;
                if (!string.IsNullOrEmpty(p6))
                    cmd.Parameters.Add(p6, OracleDbType.Varchar2, ParameterDirection.Input).Value = v6;
                if (!string.IsNullOrEmpty(p7))
                    cmd.Parameters.Add(p7, OracleDbType.Varchar2, ParameterDirection.Input).Value = v7;
                if (!string.IsNullOrEmpty(p8))
                    cmd.Parameters.Add(p8, OracleDbType.Varchar2, ParameterDirection.Input).Value = v8;
                if (!string.IsNullOrEmpty(p9))
                    cmd.Parameters.Add(p9, OracleDbType.Varchar2, ParameterDirection.Input).Value = v9;
                if (!string.IsNullOrEmpty(p10))
                    cmd.Parameters.Add(p10, OracleDbType.Varchar2, ParameterDirection.Input).Value = v10;
                if (!string.IsNullOrEmpty(p11))
                    cmd.Parameters.Add(p11, OracleDbType.Varchar2, ParameterDirection.Input).Value = v11;
                if (!string.IsNullOrEmpty(p12))
                    cmd.Parameters.Add(p12, OracleDbType.Varchar2, ParameterDirection.Input).Value = v12;
                if (!string.IsNullOrEmpty(p13))
                    cmd.Parameters.Add(p13, OracleDbType.Varchar2, ParameterDirection.Input).Value = v13;
                OracleDataAdapter adap = new OracleDataAdapter(cmd);
                adap.Fill(dt);
                return dt;
            }
            catch (Exception ee)
            {
                DataColumn c = new DataColumn("Error");
                dt.Columns.Add(c);

                DataRow workRow = dt.NewRow();
                workRow["Error"] = ee.Message;
                dt.Rows.Add(workRow);

                return dt;
            }
        }
        public static string runFuntion(string query, string returnNameInFuntion, string p1, string v1, string p2, string v2, string p3, string v3, string p4, string v4, string p5, string v5, string p6, string v6, string p7, string v7, string p8, string v8)
        {
            try
            {
                OracleCommand cmd = DBAction.con.CreateCommand();
                cmd.CommandText = query;
                cmd.CommandType = CommandType.StoredProcedure;
                if (!string.IsNullOrEmpty(returnNameInFuntion))
                    cmd.Parameters.Add(returnNameInFuntion, OracleDbType.NVarchar2).Direction = ParameterDirection.ReturnValue;
                if (!string.IsNullOrEmpty(p1))
                    cmd.Parameters.Add(p1, OracleDbType.Varchar2, ParameterDirection.Input).Value = v1;
                if (!string.IsNullOrEmpty(p2))
                    cmd.Parameters.Add(p2, OracleDbType.Varchar2, ParameterDirection.Input).Value = v2;
                if (!string.IsNullOrEmpty(p3))
                    cmd.Parameters.Add(p3, OracleDbType.Varchar2, ParameterDirection.Input).Value = v3;
                if (!string.IsNullOrEmpty(p4))
                    cmd.Parameters.Add(p4, OracleDbType.Varchar2, ParameterDirection.Input).Value = v4;
                if (!string.IsNullOrEmpty(p5))
                    cmd.Parameters.Add(p5, OracleDbType.Varchar2, ParameterDirection.Input).Value = v5;
                if (!string.IsNullOrEmpty(p6))
                    cmd.Parameters.Add(p6, OracleDbType.Varchar2, ParameterDirection.Input).Value = v6;
                if (!string.IsNullOrEmpty(p7))
                    cmd.Parameters.Add(p7, OracleDbType.Varchar2, ParameterDirection.Input).Value = v7;
                if (!string.IsNullOrEmpty(p8))
                    cmd.Parameters.Add(p8, OracleDbType.Varchar2, ParameterDirection.Input).Value = v8;
                cmd.ExecuteNonQuery();
                string st = Convert.ToString(cmd.Parameters[returnNameInFuntion].Value);
                return st;
            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return ee.InnerException.Message;
                else
                    return ee.Message;
            }
        }
       
        public static string GetCustomerCodeByBillTo(string billto)
        {
            Entities db = new Entities();
            var c = from t in db.TB_CM_BILLTO_INF
                    where t.BILLTO_CODE == billto
                    select t;
            if (c.Count() == 1)
                return c.Single().CUSTOMER_CODE;
            else
                if (c.Count() > 1)
                    return "More than 1 result found";
                else
                    return "Error while get Customer code of BillTo: " + billto;

        }
        public static string GetRegionByID(string regID)
        {
            Entities db = new Entities();
            var c = from t in db.TB_CM_REGION
                    where t.ID == regID
                    select t;
            if (c.Count() == 1)
                return c.Single().NAME;
            else
                if (c.Count() > 1)
                    return "More than 1 result found";
                else
                    return "Error while get Region Name of ID: " + regID;

        }

        public static List<Dictionary<string, object>> ToJsonStructure(this DataTable table)
        {
            if (table == null) throw new ArgumentNullException("table");

            List<Dictionary<string, object>> data = new List<Dictionary<string, object>>();
            Dictionary<string, object> obj;

            foreach (DataRow r in table.Rows)
            {
                obj = new Dictionary<string, object>();

                foreach (DataColumn c in table.Columns)
                {
                    obj[c.ColumnName] = r[c.ColumnName];
                }

                data.Add(obj);
            }

            return data;
        }

    }
    public class MessageBox
    {
        private static Hashtable m_executingPages = new Hashtable();

        private MessageBox()
        {
        }

        public static void Show(string sMessage)
        {
            if (!m_executingPages.Contains(HttpContext.Current.Handler))
            {
                Page executingPage = HttpContext.Current.Handler as Page;

                if (executingPage != null)
                {
                    Queue messageQueue = new Queue();
                    messageQueue.Enqueue(sMessage);
                    m_executingPages.Add(HttpContext.Current.Handler, messageQueue);
                    executingPage.Unload += new EventHandler(ExecutingPage_Unload);
                }
            }
            else
            {
                Queue queue = (Queue)m_executingPages[HttpContext.Current.Handler];
                queue.Enqueue(sMessage);
            }
        }

        private static void ExecutingPage_Unload(object sender, EventArgs e)
        {
            Queue queue = (Queue)m_executingPages[HttpContext.Current.Handler];
            if (queue != null)
            {
                StringBuilder sb = new StringBuilder();
                int iMsgCount = queue.Count;
                sb.Append("<script language='javascript'>");
                string sMsg;
                while (iMsgCount-- > 0)
                {
                    sMsg = (string)queue.Dequeue();
                    sMsg = sMsg.Replace("\n", "\\n");
                    sMsg = sMsg.Replace("\"", "'");
                    sb.Append(@"alert( """ + sMsg + @""" );");
                }
                sb.Append(@"</script>");
                m_executingPages.Remove(HttpContext.Current.Handler);
                HttpContext.Current.Response.Write(sb.ToString());
            }
        }

        public static void Alert(Page page, string title, string script)
        {
            ClientScriptManager cs = page.ClientScript;
            cs.RegisterClientScriptBlock(page.GetType(), title, "<script>alert('" + script + "');</script>");
        }

        public static void AlertAndRedirct(Page page, string title, string script, string location)
        {
            ClientScriptManager cs = page.ClientScript;
            cs.RegisterClientScriptBlock(page.GetType(), title, "<script>alert('" + script + "');window.location='" + location + "';</script>");
        }
    }

    public class ObjectThayThe
    {
        public string string1 { get; set; }
        public string string2 { get; set; }
        public string string3 { get; set; }
        public string string4 { get; set; }
        public string string5 { get; set; }
        public string string6 { get; set; }
        public DateTime date1 { get; set; }
        public DateTime date2 { get; set; }
        public DateTime date3 { get; set; }
        public DateTime date4 { get; set; }
        public DateTime date5 { get; set; }
        public DateTime date6 { get; set; }
    }

    public static class DataTableExtensions
    {
        public static List<Dictionary<string, object>> ToJsonStructure(this DataTable table)
        {
            if (table == null) throw new ArgumentNullException("table");

            List<Dictionary<string, object>> data = new List<Dictionary<string, object>>();
            Dictionary<string, object> obj;

            foreach (DataRow r in table.Rows)
            {
                obj = new Dictionary<string, object>();

                foreach (DataColumn c in table.Columns)
                {
                    obj[c.ColumnName] = r[c.ColumnName];
                }

                data.Add(obj);
            }

            return data;
        }

        public static List<Dictionary<string, object>> GetFieldsMetaData(this DataTable table)
        {
            if (table == null) throw new ArgumentNullException("table");

            List<Dictionary<string, object>> data = new List<Dictionary<string, object>>();
            Dictionary<string, object> obj;

            foreach (DataColumn c in table.Columns)
            {
                obj = new Dictionary<string, object>();

                obj["name"] = c.ColumnName;
                obj["type"] = GetTypeName(c.DataType);

                if (c.DataType == typeof(DateTime))
                {
                    obj["dateFormat"] = "M$";
                }

                data.Add(obj);
            }

            return data;
        }

        public static List<Dictionary<string, object>> GetColumnsMetaData(this DataTable table)
        {
            if (table == null) throw new ArgumentNullException("table");

            List<Dictionary<string, object>> data = new List<Dictionary<string, object>>();
            Dictionary<string, object> obj;

            foreach (DataColumn c in table.Columns)
            {
                obj = new Dictionary<string, object>();

                obj["header"] = c.ColumnName;
                obj["dataIndex"] = c.ColumnName;
                obj["width"] = c.DataType == typeof(string) ? 200 : 100;

                if (c.DataType == typeof(DateTime))
                {
                    obj["xtype"] = "datecolumn";
                    obj["format"] = "M d, Y";
                }

                data.Add(obj);
            }

            return data;
        }

        private static string GetTypeName(Type type)
        {
            switch (type.Name)
            {
                case "Int32": return "int";
                case "String": return "string";
                case "DateTime": return "date";
                default: return "auto";
            }
        }
    }

}